package com.baturu.vin.benz.dal.dao;

import com.baturu.vin.benz.dto.BenzPartCatInfoDTO;
import com.baturu.vin.benz.dto.BenzSaDescDTO;
import com.baturu.vin.benz.dto.BenzVinFinInfoDTO;
import com.baturu.vin.benz.dto.BenzVinInfoDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * vin码对应车型数据DAO
 * (数据库表暂时不做改变,为了以后方便更新数据的时候直接加进来方便)
 *
 * @author liangguanglong
 * @Date 16/7/11
 * @Time 下午2:10
 */
public interface BenzVinCarTypeInfoDAO {

    @Select({"select btrId from benz_vininfo_btr where chassis=#{fin.chassbm} ",
            " and `engine`=#{fin.shortEngine} ",
            " and transmission=#{fin.shortTransmission} ",
            " and productTime=#{fin.dateyear} and country=#{fin.country}",
            " and displacement=#{fin.displacement} ",
            " and transmissionType=#{fin.transmissionType} and gearsNum=#{fin.gearsNum} ",
            " and vehSteering=#{fin.vehSteering}",
            " limit 1"})
    String findBtrId(@Param("fin") BenzVinInfoDTO carTypeInfoDTO);

    @Select({" select btrId from benz_vininfo_btr where chassis=#{fin.chassbm} ",
            "and `engine`=#{fin.shortEngine} ",
            " and transmission=#{fin.shortTransmission} and productTime=#{fin.dateyear}",
            " and country=#{fin.country} ",
            " and displacement='' and transmissionType='' and gearsNum='' ",
            " and vehSteering=#{fin.vehSteering}",
            " limit 1"})
    String findBtrIdWithoutDisAndTrans(@Param("fin") BenzVinInfoDTO carTypeInfoDTO);


    @Select({"<script>select sacode sacodeex, a_desc adesc from sa_codes2_v where lang='Z' ",
            "and (a_date&lt;=#{adate} or a_date is null) and (type = '' or type is null or type=#{type}) ",
            "and sacode in  ",
            "<foreach item='item' index='index' collection='sacodes' open='(' separator=',' close=') '>",
            "#{item}",
            "</foreach>",
            "</script>"})
    List<BenzVinInfoDTO> findSaDes(@Param("adate") String adate, @Param("type") String type, @Param("sacodes") Collection<String> sacodes);


    /**
     * 查询sa 信息
     *
     * @param productDate
     * @param types
     * @param saCodes
     * @return
     */
    @Select({"<script>select sacode saCode, a_desc saDesc from sa_codes2_v where lang=#{lang} ",
            "and (a_date &lt;= #{productDate} or a_date is null)",
            " and type IN ",
            "<foreach item='type' index='index' collection='types' open='(' separator=',' close=') '>",
            "#{type}",
            "</foreach>",
            "and sacode IN  ",
            "<foreach item='saCode' index='index' collection='saCodes' open='(' separator=',' close=') '>",
            "#{saCode}",
            "</foreach>",
            "</script>"})
    List<BenzSaDescDTO> findSaDesc(@Param("productDate") String productDate,
                                   @Param("types") Collection<String> types,
                                   @Param("saCodes") Collection<String> saCodes,
                                   @Param("lang") String langCode);

    @Select("select id,whc,chassbm,chass_ident chassIdent,vinwhc,vin,location,vin8 from dc_map_v where vin=#{vin14} OR ( whc=#{whc} and chassbm= #{chassbm} and chass_ident=#{chassIdent})")
    List<BenzVinFinInfoDTO> finFinByVin14OrWhc(@Param("vin14") String vin14, @Param("whc") String whc, @Param("chassbm") String chassbm, @Param("chassIdent") String chassIdent);

    @Select("select id,whc,chassbm,chass_ident chassIdent,vinwhc,vin,location,vin8 from dc_map_v where vin=#{vin14} limit 1")
    BenzVinFinInfoDTO finFinByVin14(@Param("vin14") String vin14);

    /**
     * 通过fin信息查询vin信息
     *
     * @param fin
     * @return
     */
    @Select("select id,whc,chassbm,chass_ident chassIdent,vinwhc,vin,location,vin8 from dc_map_v where chass_ident=#{fin}")
    List<BenzVinFinInfoDTO> finVinInfosByFin(@Param("fin") String fin);

    @Select("select id,whc,chassbm,chass_ident chassIdent,vinwhc,vin,location,vin8 from dc_map_v where whc=#{whc} and chassbm= #{chassbm} and chass_ident=#{chassIdent} limit 1")
    BenzVinFinInfoDTO findByFinInfo(@Param("whc") String whc, @Param("chassbm") String chassbm, @Param("chassIdent") String chassIdent);

    @Select({"select whc,chassbm,chass_ident chassIdent,engine,transmission,delivery_date deliveryDate,release_date releaseDate,interior,paint1,paint2,paint3,paint4,paint5,",
            " steering,front_axle1 as frontAxle1,rear_axle1 as rearAxle1,REAR_AXLE2 as rearAxle2,transfer_case transferCase,body,platform,FRONT_AXLE2 as frontAxle2 ,ORDER_NUM1 as orderNum,MODEL_IDX modelIndex from dc_rtype1_v_${dbName} where",
            " whc=#{whc} and chassbm= #{chassbm} and chass_ident=#{chassIdent} limit 1"})
    BenzVinInfoDTO finCarTypeByDBName(@Param("whc") String whc, @Param("chassbm") String chassbm, @Param("chassIdent") String chassIdent, @Param("dbName") String dbName);

    @Select("select sacode_data from dc_rtype3_v_${dbName} where whc=#{fin.whc} and chassbm=#{fin.chassbm} and chass_ident=#{fin.chassIdent}")
    List<String> findSACodeByDBName(@Param("fin") BenzVinFinInfoDTO benzVinFinInfoDTO, @Param("dbName") String dbName);


    /**
     * 查询sa 码
     *
     * @param whc
     * @param chassbm
     * @param chassIdent
     * @param dbName
     * @return
     */
    @Select("select sacode_data from dc_rtype3_v_${dbName} where whc=#{whc} and chassbm=#{chassbm} and chass_ident=#{chassIdent}")
    Set<String> findSACodeByDbName(@Param("whc") String whc, @Param("chassbm") String chassbm, @Param("chassIdent") String chassIdent, @Param("dbName") String dbName);

    @Select({"<script>select m.class classNo,m.aggType,m.appInf appInfo,m.type,m.subbm1,m.subbm2,m.catnum catalog,m.salesdes,m.cattyp2,m.source,m.remarks,m.cattype",
            " from models_v m  ",
            "where m.class in('1','2','3','F') and ",
            " m.type = #{type}",
            " and m.subbm1 = #{subbm1} ",
            "and m.appInf like CONCAT('%',#{appInf},'%') ",
            "AND EXISTS (SELECT 1 FROM APPL_CAT_BASE_V c  WHERE c.catnum = m.catnum AND m.appinf = c.markets  ) </script>"})
    List<BenzPartCatInfoDTO> findCarCatNum(@Param("type") String type, @Param("subbm1") String subbm1, @Param("appInf") String appInf);

    @Select({"<script>select m.class classNo,m.aggType,m.appInf appInfo,m.type,m.subbm1,m.subbm2,m.catnum catalog,m.salesdes,m.cattyp2,m.source,m.remarks,m.cattype",
            " from models_v m  ",
            "where m.class in('1','2','3','F') and ",
            "(m.type,m.subbm1) IN ",
            "<foreach item='item' index='index' collection='list' open='(' separator=',' close=') '>",
            "(#{item.type}, #{item.subbm1})",
            "</foreach>",
            "and m.appInf like CONCAT('%',#{appInf},'%') ",
            "AND EXISTS (SELECT 1 FROM APPL_CAT_BASE_V c  WHERE c.catnum = m.catnum AND m.appinf = c.markets  ) </script>"})
    List<BenzPartCatInfoDTO> findCarCatNums(@Param("list") List<Map<String, String>> list, @Param("appInf") String appInf);

    @Select({"select location,dbName from benz_dcdbinfo_v"})
    List<Map<String, String>> findDbNameByLocation();

    @Select({"select distinct salesdes from models_v",
            "where aggType='FG' and type=#{type} and subbm1=#{subbm} and salesdes like '%AMG%' limit 1"})
    String queryAMGCarType(@Param("type") String type, @Param("subbm") String subbm);

    @Select({"select MTRSAC from dc_rtypea_v_${dbName} where whc=#{whc} and chassbm= #{chassbm} and chass_ident=#{chassIdent} limit 1"})
    String findMasterDescWithDBName(@Param("whc") String whc, @Param("chassbm") String chassbm, @Param("chassIdent") String chassIdent, @Param("dbName") String dbName);

    /**
     * @param svnr
     * @param aggBm
     * @return
     */
    @Select({"SELECT etvbm FROM benz_fdoka1_v WHERE SVNR=#{SVNR} and AGGBM=#{AGGBM} limit 1"})
    String findEtvbm(@Param("SVNR") String svnr, @Param("AGGBM") String aggBm);

    @Select({"SELECT ZUTEXT FROM benz_fdoka2_v WHERE ETVNR=#{ETVNR}  limit 1"})
    String findZuText(@Param("ETVNR") String svnr);

    @Select({"<script>select m.class classNo,m.aggType,m.appInf appInfo,m.type,m.subbm1,m.subbm2,m.catnum catalog,m.salesdes,m.cattyp2,m.source,m.remarks,m.cattype",
            " from models_v m  ",
            "where m.class in('1','2','3','F') ",
            "</script>"})
    List<BenzPartCatInfoDTO> finCarInfo(@Param("type") String type, @Param("subbm1") String subbm1, @Param("appInf") String appInf);

}
